﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kugar.Core.BaseStruct;
using Kugar.Core.ExtMethod;
using SqlSugar;
using DbType = SqlSugar.DbType;

namespace Kugar.Tools.SQLHelper.SQLGenerator
{
    public class SQLBuilder
    {
        private SqlSugarClient _client = null;

        public SQLBuilder(string connStr)
        {
            _client=new SqlSugarClient(new ConnectionConfig()
            {
                DbType = DbType.SqlServer,
                ConnectionString = connStr,
                IsAutoCloseConnection = true,
                
            });

            
        }


        public void Open()
        {
            _client.Open();

            if (_client.Ado.Connection.State!= ConnectionState.Open)
            {
                throw new Exception("连接未成功");
            }
        }

        public string[] GetAllDb()
        {
            var sql = "SELECT name FROM master.sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')";

            return _client.Ado.SqlQuery<string>(sql).ToList().ToArrayEx();
        }

        public DbTableStructInfo[] GetTables(string dbName)
        {

            var sql = $@"use [{dbName}]; 
                      select tbl.name as 表名,tbl.xtype as 类型,isnull(ds.value,'') as 注释 
                      from sysobjects tbl
                      LEFT JOIN
                      	(select * from sys.extended_properties where name='MS_Description' and isnull(minor_id,0)=0) ds ON ds.major_id=tbl.id 
                      where (xtype='u' or xtype='v')  order by tbl.name";

            var tables=_client.Ado.GetDataTable(sql);
            
            var lst=new List<DbTableStructInfo>();
            
            foreach (DataRow row in tables.Rows)
            {
                var info = GetTableColumnsStruct(dbName,row["表名"].ToStringEx());

                info.Type = (row["类型"].ToStringEx().Trim()=="U"?0:1);
                info.Name = row["表名"].ToStringEx();
                info.Comment=row["注释"].ToStringEx();

                //var template=new SQLServerTableGenerator(info);
            
                //var text=template.TransformText();
                

                lst.Add(info);
            }

            return lst.ToArray();
        }

        public DbProcInfo[] GetProcs(string dbName)
        {
            var sql = $@"
                        use [{dbName}];
                        SELECT
	                        t1.[name] AS [SP_name],
	                        t2.[name] AS [Parameter_name],
	                        t3.[name] AS [Type],
	                        t2.[isnullable] as [IsNullable],
	                        t2.[isoutparam] as IsOut,
	                        t2.[Length],
	                        t2.colorder AS [Param_order] 
                        FROM
	                        sysobjects t1
	                        INNER JOIN syscolumns t2 ON t1.[id] = t2.[id]
	                        INNER JOIN systypes t3 ON t2.xtype= t3.xtype
	                        INNER JOIN information_schema.routines r ON r.specific_name= t1.name 
                        WHERE
	                        routine_type = 'PROCEDURE' 
                        ORDER BY
	                        sp_name,
	                        [Param_order] ASC
	                        ";

            var tbl = _client.Ado.GetDataTable(sql);

            var procNames = tbl.AsEnumerable().Select(x => x["SP_name"].ToStringEx()).Distinct().ToArrayEx();

            var lst=new List<DbProcInfo>();

            foreach (var procName in procNames)
            {
                var proc = new DbProcInfo();

                proc.ProcName = procName;

                var parameters = tbl.AsEnumerable().Where(x => x["SP_name"].ToStringEx() == procName)
                    .Where(x=>x["Type"].ToStringEx() != "sysname")
                    .OrderBy(x=>x["Param_order"].ToInt())

                    .Select(x=>new DbProcInfo.ParameterInfo()
                    {
                        ParameterName = x["Parameter_name"].ToStringEx(),
                        Index =x["Param_order"].ToInt(),
                        TypeName = x["Type"].ToStringEx(),
                        Length = x["Length"].ToInt(),
                        IsOut = x["IsOut"].ToBool(),
                        IsNullable = x["IsNullable"].ToBool()
                    })
                    .ToArrayEx();

                proc.Parameters = parameters;
                
                lst.Add(proc);
            }

           // _client.Deleteable<>()
            //var s=new SugarParameter("sdf",10){Size = 2, Direction = ParameterDirection.Output};

            //_client.Ado.UseStoredProcedure().SqlQuery<int>("")

            return lst.ToArrayEx();
            
            return null;
        }

        public DbTableStructInfo GetTableColumnsStruct(string dbName,string table)
        {
            var sql =
                $@"use [{dbName}];
                   SELECT  CASE WHEN col.colorder = 1 THEN obj.name
                                ELSE ''
                            END AS 表名,
                            col.colorder AS 序号 ,
                            col.name AS 列名 ,
                            ISNULL(ep.[value], '') AS 列说明 ,
                            t.name AS 数据类型 ,
                            col.length AS 长度 ,
                            ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
                            CASE 
                                WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN 1
                            ELSE ''
                            END AS 自增量 ,
                            CASE 
                                WHEN EXISTS ( SELECT 1 
                                              FROM dbo.sysindexes si
                                              INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid 
                                              INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
                                              INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK' 
                                              WHERE sc.id = col.id AND sc.colid = col.colid ) 
                                    THEN 1 ELSE '' 
                            END AS 主键 , 
                            CASE 
                                WHEN col.isnullable = 1 THEN 1 
                                ELSE '' 
                            END AS 允许空 , 
                            ISNULL(comm.text, '') AS 默认值 
                    FROM dbo.syscolumns col 
                    LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype 
                    inner JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0
                    LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
                    LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
                    LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description'
                    WHERE obj.name = '{table}'    --表名
                    ORDER BY col.colorder ";

            var tbl = _client.Ado.GetDataTable(sql);

            var info=new DbTableStructInfo();

            var cols=new List<DbTableInfo_Column>();
            foreach (DataRow row in tbl.Rows)
            {
                var col = new DbTableInfo_Column();
                col.Name = row["列名"].ToStringEx();
                col.Comment = row["列说明"].ToStringEx();
                col.IsNullable = ((int) row["允许空"]) == 1;
                col.IsPrimaryKey= ((int)row["主键"]) == 1;
                col.IsAutoInc = ((int)row["自增量"]) == 1;

                col.ValueType = row["数据类型"].ToStringEx();
                col.Size = (int) row["长度"].ToInt();
                col.Digit = (int) row["小数位数"].ToInt();
                
                cols.Add(col);
            }

            info.Columns = cols.ToArrayEx();

            return info;
        }


    }
}
